/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Penjadwalan;

import Dosen.Dosen;
import Matakuliah.Matakuliah;
import Ruang.Ruang;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import rploo_ciayoo.kontrol.KoneksiDatabase;

/**
 *
 * @author admin
 */
public class JadwalKontrol {

    private Connection conn;

    public JadwalKontrol(Connection koneksi) {
        this.conn = koneksi;
    }

    public static JadwalKontrol getKoneksi() throws SQLException {
        KoneksiDatabase konek = new KoneksiDatabase();
        JadwalKontrol kon = new JadwalKontrol(konek.getDBConnection());
        return kon;
    }

    public void tambahJadwal(Penjadwalan jdl) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "insert into penjadwalan "
                    + "(id_dosen,id_matakuliah,id_ruang,jam,hari,kelas)"
                    + "values (?,?,?,?,?,?)";
            statement = conn.prepareStatement(sql);
            statement.setString(5, jdl.getHari());
            statement.setString(1, jdl.getIdDosen().getId_dosen());
            statement.setString(2, jdl.getIdMakul().getIdmatakuliah());
//        statement.setDouble(4,jdl.getIdMakul());
            statement.setString(3, jdl.getIdRuang().getId_ruang());
            statement.setString(4, jdl.getJam());
            statement.setString(6, jdl.getKelas());
//            statement.setDate(1, jdl.getHari());
//            statement.s(2, jdl.getIdDosen());
            statement.executeUpdate();
//             statement.executeQuery();
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }

    public void cekJadwal(Penjadwalan jdl) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "select * from penjadwalan where"
                    + " (jam = ? and hari = ? and (id_dosen = ? or id_ruang = ?))or(id_matakuliah = ? and kelas = ?)";
            statement = conn.prepareStatement(sql);
            statement.setString(2, jdl.getHari());
            statement.setString(3, jdl.getIdDosen().getId_dosen());
            statement.setString(5, jdl.getIdMakul().getIdmatakuliah());
//        statement.setDouble(4,jdl.getIdMakul());
            statement.setString(4, jdl.getIdRuang().getId_ruang());
            statement.setString(1, jdl.getJam());
            statement.setString(6, jdl.getKelas());
//            statement.setDate(1, jdl.getHari());
//            statement.s(2, jdl.getIdDosen());
            // System.out.println(jdl.getIdDosen().getId_dosen());
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                JOptionPane.showMessageDialog(null, "Jadwal Sudah Ada. ULANGI!!");
            } else {
                tambahJadwal(jdl);
                JOptionPane.showMessageDialog(null, "Data Berhasil Disimpan");
            }
//             statement.executeQuery();
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }

    public List<Penjadwalan> lihatSeluruhJadwal() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select hari,jam,kelas,id_matakuliah,id_dosen,id_ruang "
                    + "from penjadwalan order by hari desc");
            result = statement.executeQuery();
            List<Penjadwalan> penjadwalan = new ArrayList<Penjadwalan>();
//            List<Penjadwalan> penjadwalan = new ArrayList<Penjadwalan>();
            while (result.next()) {
                Penjadwalan jadwal = new Penjadwalan();
                jadwal.setHari(result.getString(1));
                jadwal.setJam(result.getString(2));
                jadwal.setKelas(result.getString(3));
                Matakuliah m = new Matakuliah();
                jadwal.setIdMakul(m);
                m.setIdMatakuliah(result.getString(4));
                Dosen dosen = new Dosen();
                jadwal.setIdDosen(dosen);
                dosen.setId_dosen(result.getString(5));
                Ruang ruang = new Ruang();
                jadwal.setIdRuang(ruang);
                ruang.setId_ruang(result.getString(6));
                penjadwalan.add(jadwal);

            }
            conn.commit();
            return penjadwalan;
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }

//    public List<Penjadwalan> seluruhData() throws SQLException {
//        PreparedStatement statement = null;
//        ResultSet result = null;
//        try {
//            conn.setAutoCommit(false);
//            statement = conn.prepareStatement("select hari,jam,kelas,id_matakuliah,id_dosen,id_ruang "
//                    + "from penjadwalan order by hari");
//            result = statement.executeQuery();
////            List<Penjadwalan> penjadwalan = new ArrayList<Penjadwalan>();
//            List<Penjadwalan> penjadwalan = new ArrayList<Penjadwalan>();
//            while (result.next()) {
//                Penjadwalan jadwal = new Penjadwalan();
//                jadwal.setHari(result.getString(1));
//                jadwal.setJam(result.getString(2));
//                jadwal.setKelas(result.getString(3));
//                Matakuliah m = new Matakuliah();
//                jadwal.setIdMakul(m);
//                m.setIdMatakuliah(result.getString(4));
//                Dosen dosen = new Dosen();
//                jadwal.setIdDosen(dosen);
//                dosen.setId_dosen(result.getString(5));
//                Ruang ruang = new Ruang();
//                jadwal.setIdRuang(ruang);
//                ruang.setId_ruang(result.getString(6));
//                penjadwalan.add(jadwal);
//            }
//            conn.commit();
//            return penjadwalan;
//        } catch (SQLException exception) {
//            conn.rollback();
//            throw exception;
//        } finally {
//            try {
//                conn.setAutoCommit(true);
//                if (statement != null) {
//                    statement.close();
//                }
//            } catch (SQLException exception) {
//                throw exception;
//            }
//        }
//    }
    public List<Penjadwalan> getNamaHari(String hari) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);

        String sql = "select hari,jam,kelas,id_matakuliah,id_dosen,id_ruang "
                + " from penjadwalan "
                + "where hari like '%" + hari + "%' ";
//                + "order by jam";

        pstmt = conn.prepareStatement(sql);
        rset = pstmt.executeQuery();
        List<Penjadwalan> p = new ArrayList<Penjadwalan>();
        while (rset.next()) {

            Penjadwalan jadwal = new Penjadwalan();
            jadwal.setHari(rset.getString(1));
            jadwal.setJam(rset.getString(2));
            jadwal.setKelas(rset.getString(3));
            Matakuliah m = new Matakuliah();
            jadwal.setIdMakul(m);
            m.setIdMatakuliah(rset.getString(4));
            Dosen dosen = new Dosen();
            jadwal.setIdDosen(dosen);
            dosen.setId_dosen(rset.getString(5));
            Ruang ruang = new Ruang();
            jadwal.setIdRuang(ruang);
            ruang.setId_ruang(rset.getString(6));
            p.add(jadwal);

        }
        conn.commit();
        return p;
    }

    public void hapusPrepared(Penjadwalan penjadwalan) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "delete from penjadwalan where hari = ? and jam =? and kelas =? and id_matakuliah =? and id_dosen =? and id_ruang =?";
            statement = conn.prepareStatement(sql);
            statement.setString(1, penjadwalan.getHari());
            statement.setString(2, penjadwalan.getJam());
            statement.setString(3, penjadwalan.getKelas());
            statement.setString(4, penjadwalan.getIdMakul().getIdmatakuliah());
            statement.setString(5, penjadwalan.getIdDosen().getId_dosen());
            statement.setString(6, penjadwalan.getIdRuang().getId_ruang());
            statement.executeUpdate();
            conn.commit();
        } catch (SQLException ex) {
            conn.rollback();
            throw ex;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                throw ex;
            }
        }
    }

    public void updatePenjadwalan(Penjadwalan jadwalLama, Penjadwalan jadwalBaru) throws SQLException {
        hapusPrepared(jadwalLama);
        cekJadwal2(jadwalBaru,jadwalLama);

    }

    public void cekJadwal2(Penjadwalan jdlbr, Penjadwalan jdllm) throws SQLException {
        PreparedStatement statement = null;
        try {
            conn.setAutoCommit(false);
            String sql = "select * from penjadwalan where"
                    + " (jam = ? and hari = ? and (id_dosen = ? or id_ruang = ?))or(id_matakuliah = ? and kelas = ?)";
            statement = conn.prepareStatement(sql);
            statement.setString(2, jdlbr.getHari());
            statement.setString(3, jdlbr.getIdDosen().getId_dosen());
            statement.setString(5, jdlbr.getIdMakul().getIdmatakuliah());
//        statement.setDouble(4,jdl.getIdMakul());
            statement.setString(4, jdlbr.getIdRuang().getId_ruang());
            statement.setString(1, jdlbr.getJam());
            statement.setString(6, jdlbr.getKelas());
//            statement.setDate(1, jdl.getHari());
//            statement.s(2, jdl.getIdDosen());
            // System.out.println(jdl.getIdDosen().getId_dosen());
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                tambahJadwal(jdllm);
                 JOptionPane.showMessageDialog(null, "JADWAL SUDAH ADA!!!!");
            } else {
                tambahJadwal(jdlbr);
                JOptionPane.showMessageDialog(null, "Data Berhasil Diupdate");
            }
//             statement.executeQuery();
            conn.commit();
        } catch (SQLException exception) {
            conn.rollback();
            throw exception;
        } finally {
            try {
                conn.setAutoCommit(true);
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException exception) {
                throw exception;
            }
        }
    }
}
